﻿using System.Data;
using System.Data.SqlClient;
using DataGeneration.Domain.Logging;
using System.Text;

namespace DataGeneration.Domain.DataAccessLayer
{
    /// <summary>
    /// Defines the type SqlServerInitializer
    /// </summary>
    public class SqlServerInitializer
    {
        /// <summary>
        /// Initializes the command.
        /// </summary>
        /// <param name="commandName">Name of the command.</param>
        /// <param name="commandType">Type of the command.</param>
        /// <param name="connection">The connection.</param>
        /// <returns></returns>
        public static IDbCommand InitializeCommand(string commandName, CommandType commandType, IDbConnection connection)
        {
            return new SqlCommand(commandName, (SqlConnection)connection) { CommandType = commandType };
        }

        /// <summary>
        /// Opens the connection.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <returns></returns>
        public static IDbConnection OpenConnection(string connectionString)
        {
            IDbConnection connection = new SqlConnection(connectionString);
            connection.Open();
            Logger.WriteMessage("Connection successfully opened!");
            return connection;
        }


        public static string SequenceUpdateSql(int startValue, int endValue,string seqencName)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendLine("Begin");
            stringBuilder.AppendLine(" declare LastValue integer := 1;");
            stringBuilder.AppendLine(" Begin");
            stringBuilder.AppendLine("  declare idx number :=1;");
            stringBuilder.AppendLine("  Begin  ");
            stringBuilder.AppendFormat("      for idx in {0}..{1} loop", startValue, endValue);
            stringBuilder.AppendLine("");
            //stringBuilder.AppendLine("           dbms_output.put_line (idx);");
            stringBuilder.AppendFormat("           Select {0}.NextVal  into LastValue from dual;", seqencName);
            stringBuilder.AppendLine("");
            stringBuilder.AppendLine("         end loop;");
            stringBuilder.AppendLine("       end;");
            stringBuilder.AppendLine("    end;");
            stringBuilder.AppendLine(" end;");

            return stringBuilder.ToString();
        }

    }
}